﻿using System;
using DMS.Base;
using DMS.Business.Utility;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace DMS.Business.Modules.Organization
{
    public class ExtendUserHelper
    {
        public static ExtendUserHelper Instance = new ExtendUserHelper();
        private ExtendUserHelper() { }
        const string SQL_INSERT_EXTENDUSER = @"INSERT INTO [dbo].[ORG_Users]
                                                                (
                                                                [UserID],
                                                                [DeptID],
                                                                [UserName],
                                                                [Sex],
                                                                [Mobile],
                                                                [UserType],
                                                                [UserPosition],
                                                                [UserStatus],
                                                                [DeptIDPath],
                                                                [orderno]
                                                                )
                                                            VALUES
                                                                (
                                                                @UserID,
                                                                @DeptID,
                                                                @UserName,
                                                                @Sex,
                                                                @Mobile,
                                                                @UserType,
                                                                @UserPosition,
                                                                @UserStatus,
                                                                @DeptIDPath,999
                                                                )";
        const string SQL_SELECT_EXSITUSER = @"SELECT *
                                                  FROM [dbo].[ORG_Users]
                                                WHERE UserName=@UserName AND Mobile=@Mobile AND USERID!=@UserID AND UserType=3";

        const string SQL_SELECT_EXTENDUSER_BY_ID = @"select [UserID],
                                                            [DeptID],
                                                            [UserName],
                                                            [Sex],
                                                            [Mobile],
                                                            [UserType],
                                                            [UserPosition],
                                                            [UserStatus],
                                                            [DeptIDPath]
                                                        from [dbo].[ORG_Users]
                                                       where userID=@UserID";

        const string SQL_Update_EXTENDUSER = @"UPDATE [dbo].[ORG_Users]
                                                    SET    [DeptID] = @DeptID,
                                                           [UserName] = @UserName,
                                                           [Sex] = @Sex,
                                                           [Mobile] = @Mobile,
                                                           [UserType] = @UserType,
                                                           [UserPosition] = @UserPosition,
                                                           [UserStatus] = @UserStatus,
                                                           [DeptIDPath] = @DeptIDPath
                                                    WHERE  [UserID] = @UserID";
        public bool SaveExtendUser(ExtendUser exUser)
        {
            if (!CheckExsitExtendUser(exUser))
            {
                Database db = DBHelper.Instance;
                DbCommand command = db.GetSqlStringCommand(SQL_INSERT_EXTENDUSER);
                command.Parameters.Add(DBHelper.CreateParamer("UserID", exUser.ID));
                command.Parameters.Add(DBHelper.CreateParamer("DeptID", exUser.DeptID));
                command.Parameters.Add(DBHelper.CreateParamer("UserName", DBHelper.getValue(exUser.UserName)));
                command.Parameters.Add(DBHelper.CreateParamer("Sex", DBHelper.getValue(exUser.Sex)));
                command.Parameters.Add(DBHelper.CreateParamer("Mobile", DBHelper.getValue(exUser.Mobile)));
                //command.Parameters.Add(DBHelper.CreateParamer("Email", DBHelper.getValue(exUser.Email)));
                command.Parameters.Add(DBHelper.CreateParamer("UserType", DBHelper.getValue(exUser.UserType)));
                command.Parameters.Add(DBHelper.CreateParamer("UserPosition", DBHelper.getValue(exUser.PositionName)));
                command.Parameters.Add(DBHelper.CreateParamer("UserStatus", DBHelper.getValue(exUser.Status)));
                command.Parameters.Add(DBHelper.CreateParamer("DeptIDPath", DBHelper.getValue(exUser.DeptIDPath)));
                db.ExecuteNonQuery(command);
                command.Dispose();
                return true;
            }
            else {
                return false;
            }
        }

        public bool UpdateExtendUser(ExtendUser exUser)
        {
            if (!CheckExsitExtendUser(exUser))
            {
                Database db = DBHelper.Instance;
                DbCommand command = db.GetSqlStringCommand(SQL_Update_EXTENDUSER);
                command.Parameters.Add(DBHelper.CreateParamer("UserID", exUser.ID));
                command.Parameters.Add(DBHelper.CreateParamer("DeptID", exUser.DeptID));
                command.Parameters.Add(DBHelper.CreateParamer("UserName", DBHelper.getValue(exUser.UserName)));
                command.Parameters.Add(DBHelper.CreateParamer("Sex", DBHelper.getValue(exUser.Sex)));
                command.Parameters.Add(DBHelper.CreateParamer("Mobile", DBHelper.getValue(exUser.Mobile)));
                command.Parameters.Add(DBHelper.CreateParamer("UserType", DBHelper.getValue(exUser.UserType)));
                command.Parameters.Add(DBHelper.CreateParamer("UserPosition", DBHelper.getValue(exUser.PositionName)));
                command.Parameters.Add(DBHelper.CreateParamer("UserStatus", DBHelper.getValue(exUser.Status)));
                command.Parameters.Add(DBHelper.CreateParamer("DeptIDPath", DBHelper.getValue(exUser.DeptIDPath)));
                db.ExecuteNonQuery(command);
                command.Dispose();
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool CheckExsitExtendUser(ExtendUser exUser) 
        {
            bool isExsit = true;
            try{
                Database db = DBHelper.Instance;
                DbCommand command = db.GetSqlStringCommand(SQL_SELECT_EXSITUSER);
                command.Parameters.Add(DBHelper.CreateParamer("UserName", DBHelper.getValue(exUser.UserName)));
                command.Parameters.Add(DBHelper.CreateParamer("Mobile", DBHelper.getValue(exUser.Mobile)));
                command.Parameters.Add(DBHelper.CreateParamer("UserID", DBHelper.getValue(exUser.ID)));
                DataSet ds = db.ExecuteDataSet(command);
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                    isExsit = true;
                else
                    isExsit = false;
                return isExsit;
            }catch(Exception ex){
                LogHelper.LogException(ex);
                return isExsit;
            }
            
            
        }

        public ExtendUser GetExtendUserByID(string userID)
        {
            Database db = DBHelper.Instance;
            DbCommand command = db.GetSqlStringCommand(SQL_SELECT_EXTENDUSER_BY_ID);
            command.Parameters.Add(DBHelper.CreateParamer("UserID", userID));
            DataSet ds = db.ExecuteDataSet(command);

            return FillExtendUser(ds);
        }

        private ExtendUser FillExtendUser(DataSet ds )
        {
            ExtendUser extendUser = null;
            DataRow dr = ds.Tables[0].Rows[0];
            if (dr == null)
                return null;
            if (!dr.IsNull("UserID"))
            {
                extendUser  = new ExtendUser(new Guid(dr["UserID"].ToString()).ToString());
                if (!dr.IsNull("DeptID"))
                {
                    Department dept = UserHelper.GetDepartmentByID(Convert.ToString(dr["DeptID"]));
                    extendUser.Dept = dept;
                }
                if (!dr.IsNull("UserName"))
                    extendUser.UserName = Convert.ToString(dr["UserName"]);
                if (!dr.IsNull("Sex"))
                    extendUser.Sex =  (SexTypes)Enum.Parse(typeof(SexTypes),Convert.ToString(dr["Sex"]));
                if (!dr.IsNull("Mobile"))
                    extendUser.Mobile = Convert.ToString(dr["Mobile"]);
                if (!dr.IsNull("UserPosition"))
                    extendUser.PositionName = Convert.ToString(dr["UserPosition"]);
                if (!dr.IsNull("UserStatus"))
                    extendUser.Status = (ActorStatus)Enum.Parse(typeof(ActorStatus),Convert.ToString(dr["UserStatus"]));
            }

            return extendUser;
        }
    }
}